"""
Case Type   : GUC
Case Name   : 管理员用户方式三修改参数track_functions为all,设置成功
Create At   : 2020/12/28
Owner       : opentestcase018
Description :
    1、查询track_function默认值;查询pg_stat_user_functions
    show track_function;
    select count(*) from pg_stat_user_functions;
    2、管理员用户方式三修改track_functions为all;查询pg_stat_user_functions
    alter database postgres set track_functions to 'all';
    show track_functions;
    3、调用函数，查询pg_stat_user_functions
    4、恢复默认值,清理环境
Expect      :
    1、显示默认值none;pg_stat_user_functions视图为空
    2、参数修改成功，校验修改后参数值为all
    3、调用函数无报错，查询pg_stat_user_functions不为空
    4、恢复默认值成功，环境清理成功
History     :
    modified by opentestcase018 at 2021/12/16:优化系统表查询语句
    Modified: 2022/05/26  modified by opentestcase013: 增加等待时间、优化格式
"""

import unittest
import os

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

LOGGER = Logger()
COMMONSH = CommonSH("PrimaryDbUser")


class GucTestCase(unittest.TestCase):
    def setUp(self):
        LOGGER.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        status = COMMONSH.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.user_node = Node("PrimaryDbUser")
        self.db_name = 'db_statistics_0046'
        self.func_name = 'func_statistics_0046'

    def test_guc(self):
        LOGGER.info('--step1.1:查询track_function默认值；expect:默认值none--')
        res = COMMONSH.execut_db_sql("show track_functions;")
        LOGGER.info(res)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], res)
        self.assertEqual("none", res.splitlines()[-2].strip())

        LOGGER.info('--step1.2:查询pg_stat_user_functions视图；expect:为空--')
        res = COMMONSH.execut_db_sql("select count(*) "
                                     "from pg_stat_user_functions;")
        LOGGER.info(res)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], res)
        self.assertEqual(int(res.splitlines()[-2].strip()), 0)

        LOGGER.info('--step2.1:管理员用户方式三修改track_functions为all;'
                    'expect:成功--')
        sql_cmd = f'''
        drop database if exists {self.db_name};
        create database {self.db_name};
        alter database {self.db_name} set track_functions to 'all';
        '''
        res = COMMONSH.execut_db_sql(sql_cmd)
        self.assertIn(self.constant.ALTER_DATABASE_SUCCESS_MSG, res)

        LOGGER.info('--step2.2:show track_functions;expect:成功--')
        sql_cmd = ("show track_functions;")
        excute_cmd = f'''source {macro.DB_ENV_PATH};\
                        gsql -d {self.db_name} -U {self.user_node.db_user}\
                        -p {self.user_node.db_port} \
                        -W {self.user_node.db_password} -c "{sql_cmd}"'''
        LOGGER.info(excute_cmd)
        result = self.user_node.sh(excute_cmd).result()
        LOGGER.info(result)
        self.assertIn("all\n", result)

        LOGGER.info('--step2.3查询pg_stat_user_functions;expect:不为空')
        sql_cmd = ("select calls from pg_stat_user_functions;")
        excute_cmd = f'''source {macro.DB_ENV_PATH};\
                gsql -d {self.db_name} -U {self.user_node.db_user}\
                -p {self.user_node.db_port} \
                -W {self.user_node.db_password} -c "{sql_cmd}"'''
        LOGGER.info(excute_cmd)
        result = self.user_node.sh(excute_cmd).result()
        LOGGER.info(result)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], result)
        num_before = result.splitlines()[-2].strip()
        LOGGER.info(num_before)

        LOGGER.info('--step3:调用函数；expect:成功--')
        sql_cmd = f'''
        drop function if exists {self.func_name}(c int);
        create or replace function {self.func_name}(c int)return number as
        b int := c;
        begin
            for i in 1..c loop
                b:= b - 1;
            end loop;
            return b;
        end;
        select {self.func_name}(5);
        select pg_sleep(60);
        '''
        excute_cmd = f'''source {macro.DB_ENV_PATH};\
                gsql -d {self.db_name} -U {self.user_node.db_user}\
                -p {self.user_node.db_port} \
                -W {self.user_node.db_password} -c "{sql_cmd}"'''
        LOGGER.info(excute_cmd)
        res = self.user_node.sh(excute_cmd).result()
        LOGGER.info(res)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], res)

        LOGGER.info('--step3:查询pg_stat_user_functions视图;expect:不为空--')
        sql_cmd = ("select calls from pg_stat_user_functions;")
        excute_cmd = f'''source {macro.DB_ENV_PATH};\
                gsql -d {self.db_name} -U {self.user_node.db_user}\
                -p {self.user_node.db_port} \
                -W {self.user_node.db_password} -c "{sql_cmd}"'''
        LOGGER.info(excute_cmd)
        res = self.user_node.sh(excute_cmd).result()
        LOGGER.info(res)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], res)
        num_alter = res.splitlines()[-2].strip()
        LOGGER.info(num_alter)
        self.assertGreater(num_alter, num_before)

    def tearDown(self):
        LOGGER.info('--step4:清理环境；expect:成功')
        LOGGER.info('--step4.1:清理数据库、function；expect:成功')
        drop_db = COMMONSH.execut_db_sql(f"drop database {self.db_name};")
        drop_func = COMMONSH.execut_db_sql(f'drop function if exists '
                                           f'{self.func_name}(c int);')
        LOGGER.info(drop_func)

        LOGGER.info('--step4.2:恢复默认值；expect:成功')
        res = COMMONSH.execut_db_sql("show track_functions;")
        if "none" != res.splitlines()[-2].strip():
            COMMONSH.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                   "track_functions='none'")
            COMMONSH.restart_db_cluster()
        status = COMMONSH.get_db_cluster_status()
        LOGGER.info(status)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_func)
        self.assertTrue("Degraded" in status or "Normal" in status)
        LOGGER.info(f'-----{os.path.basename(__file__)} end-----')
